#!/usr/bin/env perl
=pod
@描述：从mysql中查出数据，写入excel中，将excel按照附件发送出去
@作者：范红星
@日期：2014年12月9日 星期二
=cut

use strict;
use warnings;

use DBI;
use POSIX qw(strftime);
use Spreadsheet::WriteExcel;
use Encode;
use Mail::Sender;

### 日期
my $start;
my $end;
my $length = scalar(@ARGV);
if($length == 0){
	$start = strftime("%Y%m%d", localtime(time - 24*3600)); #起止时间
	$end = strftime("%Y%m%d", localtime(time - 24*3600)); #结束时间
} elsif($length == 1){
	$start = $ARGV[0];
	$end = $ARGV[0];
} elsif($length == 2){
	$start = $ARGV[0];
	$end = $ARGV[1];
} else{
	print("Please input 0 to 2 argv!");
	exit(1);
}

my %hash; # 将mysql数据装载到内存

### mysql数据库操作
my $dsn = "DBI:mysql:database=autocenter;host=10.10.77.118:3307";
my $user = "dcoperator";
my $password = "c4ca6a9349";
my $sql = "SELECT stat_date,from_source_zhname,to_block_zhname,pv,uv,dpv,duv FROM seqaccess_stat_source_to_block
			WHERE from_source_id IN (500237) AND 
			to_block_id IN (100008,100011,100021) AND 
			stat_date BETWEEN ? AND ? ORDER BY stat_date";
my $dbh = DBI->connect($dsn, $user, $password, {"RaiseError" => 1});
my $charset = "set names utf8";
$dbh->do($charset);
my $sth = $dbh->prepare($sql) or die $dbh->errstr;
$sth->execute($start,$end) or die "错误: " . $sth->errstr;
while(my $ref = $sth->fetchrow_hashref()){
	my $key = $ref->{stat_date} . "\t" . $ref->{from_source_zhname} . "\t" . $ref->{to_block_zhname};
	my $value = $ref->{pv} . "\t" . $ref->{uv} . "\t" . $ref->{dpv} . "\t" . $ref->{duv};
	$hash{$key} = $value;
}
$dbh->disconnect();

### 生成excel文件;编码问题
my $fileName = $start . "-" . $end . ".xls";
my $workbook = Spreadsheet::WriteExcel->new($fileName);
my $worksheet = $workbook->add_worksheet(decode("utf8","搜索-竞价")); # 终于解决编码问题了
my $i = 0;
my $j = 0;
$worksheet->write($i,0,decode("utf8","日期"));
$worksheet->write($i,1,decode("utf8","来源版块"));
$worksheet->write($i,2,decode("utf8","目标版块"));
$worksheet->write($i,3,decode("utf8","pv"));
$worksheet->write($i,4,decode("utf8","uv"));
$worksheet->write($i,5,decode("utf8","dpv"));
$worksheet->write($i,6,decode("utf8","duv"));
$i++;
while(my ($k,$v) = each(%hash)){
	my $str = $k . "\t" . $v;
	my @row = split(/\t/,$str);
	for my $r (@row){
		$worksheet->write($i,$j,decode("utf8",$r));
		$j++;
	}
	$j = 0;
	$i++;
}
$workbook->close();
### 发送邮件; 注意'与"的区别,多人发送
my $sender = new Mail::Sender{
	ctype => "text/plain; charset=utf-8",
	encoding => "utf-8",
	smtp => "smtp.163.com",
	from => 'fanhx9@163.com',
	auth => "LOGIN",
	TLS_allowed => "0",
	authid => 'fanhx9@163.com',
	authpwd => "0909090909",
};
$sender->MailFile(
	{
		to => 'fanhx9@163.com,fanhx825@163.com',
		cc => "",
		subject => "邮件主题",
		msg => "消息内容",
		file => $fileName,
		debug => ""
	}
);
$sender->Close();

### 结束
print("程序结束");
